package com.community.dao.impl;

import com.community.dao.NoticeDao;
import com.community.model.Notice;
import com.community.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import static com.community.controller.AdminNoticeController.log;

public class NoticeDaoImpl implements NoticeDao {
    private static final String INSERT_SQL =
            "INSERT INTO notices(title,content,publish_time) VALUES(?,?,?)";
    private static final String SELECT_ALL =
            "SELECT * FROM notices ORDER BY publish_time DESC";
    private static final String SELECT_LATEST =
            "SELECT * FROM notices ORDER BY publish_time DESC LIMIT ?";
    private static final String SELECT_BY_ID =
            "SELECT * FROM notices WHERE id = ?";
//插入公告
    @Override
    public void insert(Notice notice) {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(INSERT_SQL)) {
            pstmt.setString(1, notice.getTitle());
            pstmt.setString(2, notice.getContent());
            pstmt.setTimestamp(3,
                    new Timestamp(System.currentTimeMillis()));
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("Insert failed", e);
        }
    }
    //公告删除
    @Override
    public void delete(int id) {
        String DELETE_SQL = "DELETE FROM notices WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(DELETE_SQL)) {
            pstmt.setInt(1, id);  // 设置第1个参数为ID
            pstmt.executeUpdate(); 
        } catch (SQLException e) {
            throw new RuntimeException("删除公告失败", e);
        }
    }
//获取所有公告
    @Override
    public List<Notice> getAll() {
        List<Notice> list = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(SELECT_ALL)) {
            log.debug("执行SQL: {}", SELECT_ALL);
            while (rs.next()) {
                Notice notice = mapRow(rs);
                list.add(notice);
                log.trace("添加公告: {}", notice.getTitle());
            }
        } catch (SQLException e) {
            throw new RuntimeException("查询公告失败", e);
        }
        return list;
    }

    @Override
    public List<Notice> getLatest(int limit) {
        List<Notice> list = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SELECT_LATEST)) {
            pstmt.setInt(1, limit);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) list.add(mapRow(rs));
        } catch (SQLException e) {
            throw new RuntimeException("Query failed", e);
        }
        return list;
    }

    // 新增通过id查询公告的方法
    @Override
    public Notice getById(int id) {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(SELECT_BY_ID)) {
            pstmt.setInt(1, id);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return mapRow(rs);
                }
            }
            return null; // 未找到对应id的公告
        } catch (SQLException e) {
            throw new RuntimeException("Query by ID failed", e);
        }
    }
    private Notice mapRow(ResultSet rs) throws SQLException {
        Notice notice = new Notice();
        notice.setId(rs.getInt("id"));
        notice.setTitle(rs.getString("title"));
        notice.setContent(rs.getString("content"));
        // 处理Timestamp到LocalDateTime的转换
        Timestamp timestamp = rs.getTimestamp("publish_time");
        if (timestamp != null) {
            notice.setPublishTime(timestamp.toLocalDateTime());
        }
        log.debug("转换时间: {}", notice.getPublishTime()); // 调试时间转换
        return notice;
    }
}